package com.hxgz.steel.util;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import com.hxgz.steel.common.ServerResponse;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtil {

    private ExcelUtil() {
    }

    private final String excel2003L = ".xls";//2003- 版本的excel
    private final String excel2007U = ".xlsx";//2007+ 版本的excel

    /**
     * 根据文件后缀，自适应上传文件的版本
     */
    private HSSFWorkbook getWorkbook(InputStream inStr, String filename) throws Exception {
        HSSFWorkbook workbook;
        String fileType = filename.substring(filename.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);//2003-
        } else if (excel2007U.equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);//2007+
        } else {
            throw new Exception("解析的文件格式有误");
        }
        return workbook;
    }

    /**
     * 获取excel中的数据
     */
    public static List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {
        List<List<Object>> list = new ArrayList<List<Object>>();

        //创建Excel工作薄
        XSSFWorkbook work = new XSSFWorkbook(in);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        XSSFSheet sheet = null;
        XSSFRow row = null;
        XSSFCell cell = null;

        List<String>  picList = getPics(work);
      /*  //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }*/

        sheet = work.getSheetAt(0);
        //遍历当前sheet中的所有行
        for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j) {
                continue;
            }

            //遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                if(picList ==null || picList.isEmpty()){
                    //若excel中不包含图片
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }else{
                    //若excel中包含图片，则默认表中最后一列是图片
                    if(y == row.getLastCellNum() -1){
                        li.add(getCellValue( row.getCell(y)));
                        li.add(picList.get(j-1));
                    }else{
                        //通过getCellValue方法获取当前行每一列中的数据
                        li.add(getCellValue( row.getCell(y)));
                    }
                }
            }
            //将每一行的数据添加到list
            list.add(li);
        }
//        }
        in.close();
        return list;
    }

    /**
     * 获取每个单元格的内容
     */
    private static Object getCellValue(Cell cell) {
        Object value = null;

        DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }

   /**
     * 数据转成excel
     * @param dataList   需要转换的数据
     * @param sheetName  生成的excel表名
     * @param columnName 每一列的名字(key)
     * @return
             */
    public XSSFWorkbook dataToExcel(List<Map<String, String>> dataList, String sheetName, String[] columnName) {

        int columnNum = columnName.length;
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建第一页，并命名
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //创建第一行
        XSSFRow row = sheet.createRow(0);

        // 创建两种单元格格式
        CellStyle cs = workbook.createCellStyle();
        CellStyle cs2 = workbook.createCellStyle();
        // 创建两种字体
        XSSFFont f = workbook.createFont();
        XSSFFont f2 = workbook.createFont();
        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());


//        设置列名
        for (int i = 0; i < columnNum; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(columnName[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        int rowNum = dataList.size();
        for (int j = 1; j <= rowNum; j++) {
            //创建一行
            XSSFRow r = sheet.createRow(j);
                String str = "";
                for (int k = 0; k < columnNum; k++) {
                    XSSFCell cell = r.createCell(k);
                    str = dataList.get(j - 1).get(columnName[k]);
                    if(str != null && str.endsWith(".jpg")){
                        encapsulation(workbook,sheet,str,(short)k,j);
                    }else{
                        cell.setCellValue(str);
                    }
                }
            }
            return workbook;
    }

    public HSSFWorkbook dataToExcel2(List<List<String>> dataList, String sheetName, String[] columnName) {

        int columnNum = columnName.length;
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建第一页，并命名
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //创建第一行
        HSSFRow row = sheet.createRow(0);

        // 创建两种单元格格式
        CellStyle cs = workbook.createCellStyle();
        CellStyle cs2 = workbook.createCellStyle();
        // 创建两种字体
        HSSFFont f = workbook.createFont();
        HSSFFont f2 = workbook.createFont();
        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());


//        设置列名
        for (int i = 0; i < columnNum; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(columnName[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        int rowNum = dataList.size();
        for (int j = 1; j <= rowNum; j++) {
            //创建一行
            HSSFRow r = sheet.createRow(j);
            String str = "";
            for (int k = 0; k < columnNum; k++) {
                HSSFCell cell = r.createCell(k);
                str = dataList.get(j - 1).get(k);
                if(str != null && str.endsWith(".jpg")){
                    encapsulation2(workbook,sheet,str,(short)k,j);
                }else{
                    cell.setCellValue(str);
                }
            }
        }
        return workbook;
    }

    public static final ExcelUtil getInstance() {
        return ExcelUtilHolder.instance;
    }

    private static final class ExcelUtilHolder {
        private static final ExcelUtil instance = new ExcelUtil();
    }

    /**
     * 把图片写入excel表中
     * @param fileName  图片的地址
     * @param m
     * @param n
     */
    public void encapsulation(XSSFWorkbook wbook,XSSFSheet sheet,String fileName,short m,int n){
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;//图片
        try {
            // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            //将图片读到BufferedImage
            bufferImg = ImageIO.read(new File(fileName));
            // 将图片写入流中
            ImageIO.write(bufferImg, "png", byteArrayOut);
            // 利用HSSFPatriarch将图片写入EXCEL
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            //图片一导出到单元格B2中
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                    m, n, (short)(m+1), n+1);
            // 插入图片
            patriarch.createPicture(anchor, wbook.addPicture(byteArrayOut
                    .toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (IOException io) {
            io.printStackTrace();
            System.out.println("io erorr : " + io.getMessage());
        }
    }

    public void encapsulation2(HSSFWorkbook wbook,HSSFSheet sheet,String fileName,short m,int n){
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;//图片
        try {
            // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            //将图片读到BufferedImage
            bufferImg = ImageIO.read(new File(fileName));
            // 将图片写入流中
            ImageIO.write(bufferImg, "png", byteArrayOut);
            // 利用HSSFPatriarch将图片写入EXCEL
//            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            HSSFPatriarch drawingPatriarch = sheet.createDrawingPatriarch();
            //图片一导出到单元格B2中
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                    m, n, (short)(m+1), n+1);
            // 插入图片
//            patriarch.createPicture(anchor, wbook.addPicture(byteArrayOut
//                    .toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
            drawingPatriarch.createPicture(anchor, wbook.addPicture(byteArrayOut
                    .toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (IOException io) {
            io.printStackTrace();
            System.out.println("io erorr : " + io.getMessage());
        }
    }

    /**
     * 上传带有图片的excel
     */
    public static String uploadExcel(MultipartFile file){
        //1.将上传的excel中的图片进行提取并单独保存
        FileInputStream is = null;
        try {
            String str=file.getOriginalFilename();
            is = new FileInputStream(str);
            ExcelUtil util = ExcelUtil.getInstance();
            List<Object> rowList = null;
            List<List<Object>> list = readExcelData(is, str);

            for (int i = 0; i < list.size(); i++) {
                Object[] row = list.get(i).toArray();
                if (row != null && row.length == 3) {
                   /* String s = row[0] == null ? "" : row[0].toString();
                    String ss = row[1] == null ? "" : row[1].toString();
                    if(row[2] != null   ){

                    }*/
                   System.out.println(row[0] + " -- "+ row[1] + " --- " + row[2]);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //excel中保存图片的路径
        return "success";
    }


    /**
     * 获取excel中的picture
     * @param work
     * @return
     */
    public static List<String> getPics(XSSFWorkbook work){
        List<String> list = new ArrayList<>();
        List<XSSFPictureData> pictures = work.getAllPictures();
        if(pictures == null || pictures.isEmpty()){
            return list;
        }
        XSSFSheet sheet = (XSSFSheet) work.getSheetAt(0);
        Map<String,Object> map = new HashMap<String,Object>();
        int i= 0;//图片的集合的角标
        for (XSSFShape shape : sheet.getDrawingPatriarch().getShapes()) {
            XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
            if (shape instanceof XSSFPicture) {
//                XSSFPicture pic = (XSSFPicture) shape;
                int row = anchor.getRow2();
                int col = anchor.getCol2();
//                int pictureIndex = pic.getPictureIndex()-1;
//                XSSFPictureData picData = pictures.get(pictureIndex);
                XSSFPictureData picData = pictures.get(i++);
                map.put(row+":"+col, picData);
                try{
                    String ss = savePic(String.valueOf(System.currentTimeMillis()), picData);
                    list.add(ss);
                }catch(Exception e){
                }
            }
        }
        return list;
    }

    /**
     * 保存图片
     * @param i
     * @param pic
     * @return
     * @throws Exception
     */
    private static String savePic(String i, XSSFPictureData pic) throws Exception {
        String ext = pic.suggestFileExtension();
        byte[] data = pic.getData();
        String picPath = PropertiesUtil.getProperty("ftp.server.http.prefix") + i+".jpg";
        if (ext.equals("jpeg") || ext.equals("png")) {
            FileOutputStream out = new FileOutputStream(picPath);
            out.write(data);
            out.close();
        }
        return picPath;
    }


    /**
     * 导出excel
     * @param workbook  要导出excel对象
     * @param response
     * @param excelName 导出名称
     * @return
     */
    public String exportExcel(Workbook workbook,HttpServletResponse response,String excelName){
        OutputStream output = null;
        try {
            response.reset();//面的头 meta需要 response.reset() 来清除首部的空白行
            output = response.getOutputStream();
            //            //response.setHeader 是用来设置返回页 信息
            response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.encodeURL(new String(excelName.getBytes("gb2312"), "iso8859-1"));
//            response.setContentType("application/octet-stream");
//            excelName = java.net.URLEncoder.encode(excelName, "UTF-8");
//            response.setHeader("Content-Type","application/msexcel");
//            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", excelName));
            response.setHeader("Content-Disposition", "attachment;filename=" + response.encodeURL(new String(excelName.getBytes("gb2312"), "iso8859-1")));
//            response.setContentType("application/msexcel");
//            response.setContentType("application/x-download;charset=utf-8");
//            response.setContentType("application/vnd.ms-excel;charset=utf-8");
//            response.flushBuffer();
//            response.setCharacterEncoding("UTF-8");
            workbook.write(output);
            return "导出成功";
        } catch (Exception e) {
            return "导出失败";
        }finally {
            if (output != null){
                try {
                    output.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }



}